package pandora.data.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import pandora.connection.ConnectionManager;
import pandora.data.IAccountDAO;
import pandora.data.ICustomerDAO;
import pandora.dto.CustomerDTO;



public class JDBCCustomerDAO implements ICustomerDAO {
	
	private Connection conn;
	
	public JDBCCustomerDAO(){
		conn = ConnectionManager.getInstance().checkOut();
	}
	

    protected void finalize() {
        ConnectionManager.getInstance().checkIn(conn);
    }
    
	//DCC: Método necesario para mi BO
	public CustomerDTO getCustomerByDNI(String customerDNI){
		CustomerDTO customer = new CustomerDTO();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try{

			String customerOID = selectCustomerOIDByDNI(customerDNI);
			
			if(customerOID!=null){
				String sql = "SELECT C.* FROM Customers C "+
								"WHERE (C.OID = ?)";
				
				stmt = conn.prepareStatement(sql);
	            stmt.setString(1, customerOID);

				rs = stmt.executeQuery();
			
				while(rs.next()){
					customer.setCustomerDNI(rs.getString("customerDNI"));
					customer.setCustomerName(rs.getString("customerName"));
					customer.setCustomerLastName(rs.getString("customerLastName"));
					customer.setCustomerAddress(rs.getString("customerAddress"));
				}
			}			
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return customer;
	}
	
	public String selectCustomerOIDByDNI(String customerDNI){
		  PreparedStatement stmt = null;
		  ResultSet result = null;
		  String oid = null;
		  String sql = "SELECT C.* FROM Customers C WHERE (C.customerDNI = ?) ";

	        try {
	        	
	        	stmt = conn.prepareStatement(sql);
	        	
	            stmt.setString(1, customerDNI);
	            result = stmt.executeQuery();

	            if (result.next()) {
	                oid = result.getString("OID");
	            }
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        return oid;
	}

	public CustomerDTO selectCustomerByAccount(String usr) {
		CustomerDTO customer = new CustomerDTO();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT C.* FROM Customers C, Accounts A "+
						"WHERE (A.user = ?) "+
						"AND (A.OID = C.accountOID);";
		
		try{
			stmt = conn.prepareStatement(sql);
	        stmt.setString(1, usr);

	        rs = stmt.executeQuery();
			
	        if(rs.next()){
	        	customer.setCustomerDNI(rs.getString("customerDNI"));
	        	customer.setCustomerName(rs.getString("customerName"));
	        	customer.setCustomerLastName(rs.getString("customerLastName"));
	        	customer.setCustomerAddress(rs.getString("customerAddress"));
	        }			
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return customer;
	}
	//DCC: Fin método
	
	public boolean isCustomer(String usr) {
		boolean res = false;
		
		IAccountDAO accountDAO = new JDBCAccountDAO();
		
		Statement stmt = null;
		ResultSet rs = null;
		
		try{

			String accountOID = accountDAO.selectAccountOIDByUser(usr);
			
			stmt = conn.createStatement();
			
			String sql = "select * from Customers where accountOID =" + accountOID;
			
			rs = stmt.executeQuery(sql);
						
			while (rs.next()){
				res = true;
			}
			
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }		
		
		return res;
	}	
}
